
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 10/07/2012 15:31:34
-- Generated from EDMX file: C:\Users\fire1991man\Desktop\test\itProjectsOnline\itProjectsOnline\itProjectDB.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [itProjectDB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_MilestoneTask]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tasks] DROP CONSTRAINT [FK_MilestoneTask];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectMilestone]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Milestones] DROP CONSTRAINT [FK_ProjectMilestone];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Projects]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Projects];
GO
IF OBJECT_ID(N'[dbo].[Tasks]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tasks];
GO
IF OBJECT_ID(N'[dbo].[Milestones]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Milestones];
GO
IF OBJECT_ID(N'[dbo].[Task1]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Task1];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Projects'
CREATE TABLE [dbo].[Projects] (
    [id] uniqueidentifier  NOT NULL,
    [name] nchar(255)  NOT NULL,
    [userID] uniqueidentifier  NOT NULL,
    [startDate] time  NOT NULL,
    [dueDate] time  NOT NULL,
    [customerID] uniqueidentifier  NOT NULL,
    [description] nvarchar(max)  NOT NULL,
    [statusID] uniqueidentifier  NOT NULL,
    [typeID] uniqueidentifier  NOT NULL,
    [managerID] uniqueidentifier  NOT NULL,
    [sourceID] uniqueidentifier  NOT NULL,
    [companyID] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Tasks'
CREATE TABLE [dbo].[Tasks] (
    [id] uniqueidentifier  NOT NULL,
    [name] nchar(255)  NOT NULL,
    [statusID] uniqueidentifier  NOT NULL,
    [startDate] time  NOT NULL,
    [dueDate] time  NOT NULL,
    [description] nvarchar(max)  NOT NULL,
    [milestoneID] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Milestones'
CREATE TABLE [dbo].[Milestones] (
    [id] uniqueidentifier  NOT NULL,
    [name] nchar(255)  NOT NULL,
    [statusID] uniqueidentifier  NOT NULL,
    [startDate] time  NOT NULL,
    [dueDate] time  NOT NULL,
    [description] nvarchar(max)  NOT NULL,
    [projectID] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Bugs'
CREATE TABLE [dbo].[Bugs] (
    [id] uniqueidentifier  NOT NULL,
    [name] nchar(255)  NOT NULL,
    [statusID] uniqueidentifier  NOT NULL,
    [startDate] time  NOT NULL,
    [dueDate] time  NOT NULL,
    [description] nvarchar(max)  NOT NULL,
    [milestoneID] uniqueidentifier  NOT NULL,
    [riskRate] smallint  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [id] uniqueidentifier  NOT NULL,
    [nickname] char(20)  NOT NULL,
    [roleID] uniqueidentifier  NOT NULL,
    [careerID] uniqueidentifier  NOT NULL,
    [passwordHash] char(24)  NOT NULL
);
GO

-- Creating table 'UserDetails'
CREATE TABLE [dbo].[UserDetails] (
    [id] uniqueidentifier  NOT NULL,
    [fullName] nvarchar(max)  NOT NULL,
    [email] nvarchar(max)  NOT NULL,
    [phoneNumber] varchar(20)  NOT NULL,
    [birthday] time  NOT NULL,
    [address] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'CompanyDetails'
CREATE TABLE [dbo].[CompanyDetails] (
    [id] uniqueidentifier  NOT NULL,
    [name] nvarchar(max)  NOT NULL,
    [description] nvarchar(max)  NOT NULL,
    [email] nvarchar(max)  NOT NULL,
    [address] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'User_Task'
CREATE TABLE [dbo].[User_Task] (
    [userID] uniqueidentifier  NOT NULL,
    [taskID] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'User_Bug'
CREATE TABLE [dbo].[User_Bug] (
    [userID] uniqueidentifier  NOT NULL,
    [bugID] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Roles'
CREATE TABLE [dbo].[Roles] (
    [id] uniqueidentifier  NOT NULL,
    [roleName] nvarchar(24)  NOT NULL,
    [permission] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Status'
CREATE TABLE [dbo].[Status] (
    [id] uniqueidentifier  NOT NULL,
    [name] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [id] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [PK_Projects]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'Tasks'
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [PK_Tasks]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'Milestones'
ALTER TABLE [dbo].[Milestones]
ADD CONSTRAINT [PK_Milestones]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'Bugs'
ALTER TABLE [dbo].[Bugs]
ADD CONSTRAINT [PK_Bugs]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'UserDetails'
ALTER TABLE [dbo].[UserDetails]
ADD CONSTRAINT [PK_UserDetails]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'CompanyDetails'
ALTER TABLE [dbo].[CompanyDetails]
ADD CONSTRAINT [PK_CompanyDetails]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [userID], [taskID] in table 'User_Task'
ALTER TABLE [dbo].[User_Task]
ADD CONSTRAINT [PK_User_Task]
    PRIMARY KEY NONCLUSTERED ([userID], [taskID] ASC);
GO

-- Creating primary key on [userID], [bugID] in table 'User_Bug'
ALTER TABLE [dbo].[User_Bug]
ADD CONSTRAINT [PK_User_Bug]
    PRIMARY KEY NONCLUSTERED ([userID], [bugID] ASC);
GO

-- Creating primary key on [id] in table 'Roles'
ALTER TABLE [dbo].[Roles]
ADD CONSTRAINT [PK_Roles]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'Status'
ALTER TABLE [dbo].[Status]
ADD CONSTRAINT [PK_Status]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [milestoneID] in table 'Tasks'
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [FK_MilestoneTask]
    FOREIGN KEY ([milestoneID])
    REFERENCES [dbo].[Milestones]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MilestoneTask'
CREATE INDEX [IX_FK_MilestoneTask]
ON [dbo].[Tasks]
    ([milestoneID]);
GO

-- Creating foreign key on [projectID] in table 'Milestones'
ALTER TABLE [dbo].[Milestones]
ADD CONSTRAINT [FK_ProjectMilestone]
    FOREIGN KEY ([projectID])
    REFERENCES [dbo].[Projects]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectMilestone'
CREATE INDEX [IX_FK_ProjectMilestone]
ON [dbo].[Milestones]
    ([projectID]);
GO

-- Creating foreign key on [companyID] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [FK_ProjectCompanyDetail]
    FOREIGN KEY ([companyID])
    REFERENCES [dbo].[CompanyDetails]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectCompanyDetail'
CREATE INDEX [IX_FK_ProjectCompanyDetail]
ON [dbo].[Projects]
    ([companyID]);
GO

-- Creating foreign key on [managerID] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [FK_ProjectUserDetail]
    FOREIGN KEY ([managerID])
    REFERENCES [dbo].[UserDetails]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectUserDetail'
CREATE INDEX [IX_FK_ProjectUserDetail]
ON [dbo].[Projects]
    ([managerID]);
GO

-- Creating foreign key on [customerID] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [FK_UserDetailProject]
    FOREIGN KEY ([customerID])
    REFERENCES [dbo].[UserDetails]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserDetailProject'
CREATE INDEX [IX_FK_UserDetailProject]
ON [dbo].[Projects]
    ([customerID]);
GO

-- Creating foreign key on [taskID] in table 'User_Task'
ALTER TABLE [dbo].[User_Task]
ADD CONSTRAINT [FK_User_TaskTask]
    FOREIGN KEY ([taskID])
    REFERENCES [dbo].[Tasks]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_User_TaskTask'
CREATE INDEX [IX_FK_User_TaskTask]
ON [dbo].[User_Task]
    ([taskID]);
GO

-- Creating foreign key on [userID] in table 'User_Task'
ALTER TABLE [dbo].[User_Task]
ADD CONSTRAINT [FK_User_TaskUser]
    FOREIGN KEY ([userID])
    REFERENCES [dbo].[Users]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [userID] in table 'User_Bug'
ALTER TABLE [dbo].[User_Bug]
ADD CONSTRAINT [FK_User_BugUser]
    FOREIGN KEY ([userID])
    REFERENCES [dbo].[Users]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [bugID] in table 'User_Bug'
ALTER TABLE [dbo].[User_Bug]
ADD CONSTRAINT [FK_BugUser_Bug]
    FOREIGN KEY ([bugID])
    REFERENCES [dbo].[Bugs]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BugUser_Bug'
CREATE INDEX [IX_FK_BugUser_Bug]
ON [dbo].[User_Bug]
    ([bugID]);
GO

-- Creating foreign key on [roleID] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [FK_RoleUser]
    FOREIGN KEY ([roleID])
    REFERENCES [dbo].[Roles]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoleUser'
CREATE INDEX [IX_FK_RoleUser]
ON [dbo].[Users]
    ([roleID]);
GO

-- Creating foreign key on [managerID] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [FK_UserProject]
    FOREIGN KEY ([managerID])
    REFERENCES [dbo].[Users]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserProject'
CREATE INDEX [IX_FK_UserProject]
ON [dbo].[Projects]
    ([managerID]);
GO

-- Creating foreign key on [companyID] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [FK_UserProject1]
    FOREIGN KEY ([companyID])
    REFERENCES [dbo].[Users]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserProject1'
CREATE INDEX [IX_FK_UserProject1]
ON [dbo].[Projects]
    ([companyID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------